package org.rails.core.sql;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.rails.core.util.ArrayUtil;
import org.rails.core.util.Utils;

public abstract class Query {

	protected List<String> condList = new ArrayList<String>();
	protected Map<String, Object> parameter = new HashMap<String, Object>();
	
	public final static String START_CONDS = "startConds";
	public final static String CONDS = "conds";
	public final static String VALUE_FORMAT = "valueFormat";
	public final static String COLUMN_FORMAT = "columnFormat";
	public final static String FORMAT = "format";
	public final static String OPERATOR = "operator";
	public final static String START_OPERATOR = "startOperator";
	public final static String TABLE_NAME = "tableName";
	public final static String BETWEEN = "between";
	

	protected String conditions;
	protected Map<String, Object> opt;
	protected Map<String, Object> search;
	protected List<String> fieldMaps;
	protected List<String> fields = new ArrayList<String>();
	protected List<String> columns = new ArrayList<String>();
	
	protected String startConds;
	protected String conds;
	protected String valueFormat;
	protected String columnFormat;
	protected String format;
	protected String operator;
	protected String startOperator;
	protected String tableName;
	protected boolean between = false;

	public Query(String startConds, Map<String, Object> search,
			List<String> fieldMaps, Map<String, Object> opt) {
		super();
		this.opt = (opt == null ? new HashMap<String, Object>() : opt);
		this.startConds = startConds;
		this.search = search;
		this.fieldMaps = fieldMaps;
		init();
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	private void init() {
		conds = Utils.def(opt.get(CONDS), "[column] LIKE [0]").toString();
		valueFormat = Utils.def(opt.get(VALUE_FORMAT), "%[value]%").toString();
		columnFormat = Utils.def(opt.get(COLUMN_FORMAT), "[column]").toString();
		operator = Utils.def(opt.get(OPERATOR), "AND").toString();
		tableName = Utils.def(opt.get(TABLE_NAME), "").toString();
		format = Utils.def(opt.get(FORMAT), "[conditions]").toString();
		startOperator = Utils.def(opt.get(START_OPERATOR), "AND").toString();
		between = (Boolean) Utils.def(opt.get(BETWEEN), false);

		for (String fieldMap : fieldMaps) {
			final String[] fms = fieldMap.split("\\|");
			final String field = fms[0].trim();
			String column = field;
			String cond = null;
			if (fms.length != 1)
				column = fms[1].trim();

			fields.add(field);
			columns.add(column);
			Object value = search.get(fms[0]);
			if (between && !Utils.isEmpty(value)) {
				List<Object> ranges = (List) value;
				Object range0 = ranges.get(0);
				Object range1 = ranges.get(1);
				if (!Utils.isEmpty(range0) && !Utils.isEmpty(range1)) {
					cond = "[column] BETWEEN [0] AND [1]";
					if (!Utils.isEmpty(columnFormat)) {
						cond = cond.replaceAll("\\[column\\]", columnFormat);
					}
					if (Utils.isEmpty(tableName))
						cond = cond.replaceAll("\\[column\\]", column);
					else
						cond = cond.replaceAll("\\[column\\]", tableName + "."
								+ column);

					cond = cond.replaceAll("\\[0\\]", ":" + field + "_0");
					parameter.put(field + "_0", range0);
					cond = cond.replaceAll("\\[1\\]", ":" + field + "_1");
					parameter.put(field + "_1", range1);
					condList.add(cond);
					
				} else if (!Utils.isEmpty(range0)) {
					cond = "[column] >= [0]";
					if (!Utils.isEmpty(columnFormat)) {
						cond = cond.replaceAll("\\[column\\]", columnFormat);
					}
					if (Utils.isEmpty(tableName))
						cond = cond.replaceAll("\\[column\\]", column);
					else
						cond = cond.replaceAll("\\[column\\]", tableName + "."
								+ column);
					cond = cond.replaceAll("\\[0\\]", ":" + field + "_0");
					parameter.put(field + "_0", range0);
					condList.add(cond);
					
				} else if (!Utils.isEmpty(range1)){
					cond = "[column] <= [1]";
					if (!Utils.isEmpty(columnFormat)) {
						cond = cond.replaceAll("\\[column\\]", columnFormat);
					}
					if (Utils.isEmpty(tableName))
						cond = cond.replaceAll("\\[column\\]", column);
					else
						cond = cond.replaceAll("\\[column\\]", tableName + "."
								+ column);
					cond = cond.replaceAll("\\[1\\]", ":" + field + "_1");
					parameter.put(field + "_1", range1);
					condList.add(cond);
				}					
			
			} else if(Utils.def(value,"").toString().trim().toUpperCase().equals("N/A")){
				cond = "([column] IS NULL OR [column] = '')";
				if (Utils.isEmpty(tableName))
					cond = cond.replaceAll("\\[column\\]", column);
				else
					cond = cond.replaceAll("\\[column\\]", tableName + "."
							+ column);
				condList.add(cond);		
				
			}else if(!Utils.isEmpty(value)) {
				if (!Utils.isEmpty(columnFormat)) {
					cond = conds.replaceAll("\\[column\\]", columnFormat);
				}
				if (Utils.isEmpty(tableName))
					cond = cond.replaceAll("\\[column\\]", column);
				else
					cond = cond.replaceAll("\\[column\\]", tableName + "." + column);

				cond = cond.replaceAll("\\[0\\]", ":" + field);
				if(valueFormat.equals("[value]"))
					parameter.put(field, value.toString().trim());
				else
					parameter.put(field, valueFormat.replaceAll("\\[value\\]",value.toString()));
				
				condList.add(cond);		
				
			}
			
		}
		
		if(condList.size() > 0){
			conditions = ArrayUtil.join(condList," " + operator + " ").trim();
			conditions = format.replaceAll("\\[conditions\\]",conditions);
			if(!Utils.isEmpty(startConds) && !Utils.isEmpty(conditions))
				conditions = startOperator + " " + conditions; 
		}else{
			conditions = "";
			parameter.clear();
		}
	}

	public String getConditions() {
		return conditions;
	}

	public Map<String, Object> getParameter() {
		return parameter;
	}

	public static Query between(String startConds,String startOperator,String tableName,
			Map<String,Object> search,List<String> fieldMaps,Map<String,Object> opt) {
		opt = (opt == null ? new HashMap<String, Object>() : opt);
		opt.put("tableName", tableName);
		opt.put("between", true);
		opt.put("startOperator",startOperator);
		if(Utils.def(opt.get("operator"),"").toString().trim().toUpperCase().equals("OR")){
			opt.put("format", "([conditions])");
		}
		return new Query(startConds, search, fieldMaps, opt){};
	}
	
	public static Query anyWith(String startConds,String startOperator,String tableName,
			Map<String,Object> search,List<String> fieldMaps,Map<String,Object> opt) {
		opt = (opt == null ? new HashMap<String, Object>() : opt);
		opt.put("tableName", tableName);
		opt.put("conds", "[column] LIKE [0]");
		opt.put("valueFormat","%[value]%");
		opt.put("startOperator",startOperator);
		if(Utils.def(opt.get("operator"),"").toString().trim().toUpperCase().equals("OR")){
			opt.put("format", "([conditions])");
		}
		return new Query(startConds, search, fieldMaps, opt){};
	}
	
	public static Query startsWith(String startConds,String startOperator,String tableName,
			Map<String,Object> search,List<String> fieldMaps,Map<String,Object> opt) {
		opt = (opt == null ? new HashMap<String, Object>() : opt);
		opt.put("tableName", tableName);
		opt.put("conds", "[column] LIKE [0]");
		opt.put("valueFormat","[value]%");
		opt.put("startOperator",startOperator);
		if(Utils.def(opt.get("operator"),"").toString().trim().toUpperCase().equals("OR")){
			opt.put("format", "([conditions])");
		}
		return new Query(startConds, search, fieldMaps, opt){};
	}
	
	public static Query endsWith(String startConds,String startOperator,String tableName,
			Map<String,Object> search,List<String> fieldMaps,Map<String,Object> opt) {
		opt = (opt == null ? new HashMap<String, Object>() : opt);
		opt.put("tableName", tableName);
		opt.put("conds", "[column] LIKE [0]");
		opt.put("valueFormat","%[value]");
		opt.put("startOperator",startOperator);
		if(Utils.def(opt.get("operator"),"").toString().trim().toUpperCase().equals("OR")){
			opt.put("format", "([conditions])");
		}
		return new Query(startConds, search, fieldMaps, opt){};
	}
	
	public static Query gt(String startConds,String startOperator,String tableName,
			Map<String,Object> search,List<String> fieldMaps,Map<String,Object> opt) {
		opt = (opt == null ? new HashMap<String, Object>() : opt);
		opt.put("tableName", tableName);
		opt.put("conds", "[column] > [0]");
		opt.put("valueFormat","[value]");
		opt.put("startOperator",startOperator);
		if(Utils.def(opt.get("operator"),"").toString().trim().toUpperCase().equals("OR")){
			opt.put("format", "([conditions])");
		}
		return new Query(startConds, search, fieldMaps, opt){};
	}	
	
	public static Query ge(String startConds,String startOperator,String tableName,
			Map<String,Object> search,List<String> fieldMaps,Map<String,Object> opt) {
		opt = (opt == null ? new HashMap<String, Object>() : opt);
		opt.put("tableName", tableName);
		opt.put("conds", "[column] >= [0]");
		opt.put("valueFormat","[value]");
		opt.put("startOperator",startOperator);
		if(Utils.def(opt.get("operator"),"").toString().trim().toUpperCase().equals("OR")){
			opt.put("format", "([conditions])");
		}
		return new Query(startConds, search, fieldMaps, opt){};
	}
	
	public static Query lt(String startConds,String startOperator,String tableName,
			Map<String,Object> search,List<String> fieldMaps,Map<String,Object> opt) {
		opt = (opt == null ? new HashMap<String, Object>() : opt);
		opt.put("tableName", tableName);
		opt.put("conds", "[column] < [0]");
		opt.put("valueFormat","[value]");
		opt.put("startOperator",startOperator);
		if(Utils.def(opt.get("operator"),"").toString().trim().toUpperCase().equals("OR")){
			opt.put("format", "([conditions])");
		}
		return new Query(startConds, search, fieldMaps, opt){};
	}
	
	public static Query le(String startConds,String startOperator,String tableName,
			Map<String,Object> search,List<String> fieldMaps,Map<String,Object> opt) {
		opt = (opt == null ? new HashMap<String, Object>() : opt);
		opt.put("tableName", tableName);
		opt.put("conds", "[column] <= [0]");
		opt.put("valueFormat","[value]");
		opt.put("startOperator",startOperator);
		if(Utils.def(opt.get("operator"),"").toString().trim().toUpperCase().equals("OR")){
			opt.put("format", "([conditions])");
		}
		return new Query(startConds, search, fieldMaps, opt){};
	}
	
	public static Query eq(String startConds,String startOperator,String tableName,
			Map<String,Object> search,List<String> fieldMaps,Map<String,Object> opt) {
		opt = (opt == null ? new HashMap<String, Object>() : opt);
		opt.put("tableName", tableName);
		opt.put("conds", "[column] = [0]");
		opt.put("valueFormat","[value]");
		opt.put("startOperator",startOperator);
		if(Utils.def(opt.get("operator"),"").toString().trim().toUpperCase().equals("OR")){
			opt.put("format", "([conditions])");
		}
		return new Query(startConds, search, fieldMaps, opt){};
	}
	
	public static Query in(String startConds,String startOperator,String tableName,
			Map<String,Object> search,List<String> fieldMaps,Map<String,Object> opt) {
		opt = (opt == null ? new HashMap<String, Object>() : opt);
		opt.put("tableName", tableName);
		opt.put("between",false);
		opt.put("conds", "[column] IN ([0])");
		opt.put("valueFormat","[value]");
		opt.put("startOperator",startOperator);
		if(Utils.def(opt.get("operator"),"").toString().trim().toUpperCase().equals("OR")){
			opt.put("format", "([conditions])");
		}
		return new Query(startConds, search, fieldMaps, opt){};
	}
	
	public static Query notIn(String startConds,String startOperator,String tableName,
			Map<String,Object> search,List<String> fieldMaps,Map<String,Object> opt) {
		opt = (opt == null ? new HashMap<String, Object>() : opt);
		opt.put("tableName", tableName);
		opt.put("between",false);
		opt.put("conds", "[column] NOT IN ([0])");
		opt.put("valueFormat","[value]");
		opt.put("startOperator",startOperator);
		if(Utils.def(opt.get("operator"),"").toString().trim().toUpperCase().equals("OR")){
			opt.put("format", "([conditions])");
		}
		return new Query(startConds, search, fieldMaps, opt){};
	}

}
